package cdu.sl.repository.basic;

import cdu.sl.pojo.entity.basic.Student;
import cdu.sl.pojo.vo.StudentGeneralReviewVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface StudentRepository extends JpaRepository<Student, Long> {

    @Query(value = "SELECT COUNT(*) " +
            "FROM tb_student s " +
            "JOIN tb_class c ON s.class_id = c.id " +
            "WHERE c.school_id = :schoolId", nativeQuery = true)
    long countBySchoolId(Long schoolId);

    /**
     * 统计某个班级人数
     * @param classId 班级id
     */
    long countByClassId(Long classId);


    /**
     * 学校管理员查询学生数据
     * @param schoolId 学校id
     * @param stage 学段
     * @param grade 年级
     * @param classId 班级id
     * @param testYear 测试年度
     * @param pageable 分页参数
     */
    @Query("SELECT new cdu.sl.pojo.vo.StudentGeneralReviewVO(" +
            "CAST(s.id AS LONG), " +
            "CAST(tas.id AS LONG), " +
            "s.name, " +
            "s.studentCode, " +
            "CAST(s.gender AS INTEGER), " +
            "CAST(CASE WHEN tas.id IS NULL THEN 0 ELSE 1 END AS INTEGER), " +
            "COALESCE(CAST(tas.totalScore AS FLOAT), 0), " +
            "CAST(COALESCE(tas.grade, 0) AS INTEGER), " +
            "CAST(CASE " +
            "    WHEN g.stage = 0 THEN g.grade " +
            "    WHEN g.stage = 1 THEN g.grade + 6 " +
            "    WHEN g.stage = 2 THEN g.grade + 9 " +
            "    WHEN g.stage = 3 THEN g.grade + 12 " +
            "    ELSE 0 " +
            "END AS INTEGER)) " +
            "FROM Student s " +
            "LEFT JOIN TestAssessmentSummary tas ON s.id = tas.studentId AND tas.testYear = :testYear " +
            "LEFT JOIN Clazz c ON s.classId = c.id " +
            "LEFT JOIN Grade g ON c.gradeId = g.id " +
            "WHERE c.schoolId = :schoolId " +
            "AND (:stage IS NULL OR g.stage = :stage) " +
            "AND (:grade IS NULL OR g.grade = :grade) " +
            "AND (:classId IS NULL OR s.classId = :classId)")
    Page<StudentGeneralReviewVO> findStudentGeneralReviewsForAdmin(
            @Param("schoolId") Long schoolId,
            @Param("stage") Integer stage,
            @Param("grade") Integer grade,
            @Param("classId") Long classId,
            @Param("testYear") Integer testYear,
            Pageable pageable);

    /**
     * 班主任查询
     * @param classId 班级id
     * @param testYear 测试年度
     * @param pageable 分页参数
     */
    @Query("SELECT new cdu.sl.pojo.vo.StudentGeneralReviewVO(" +
            "CAST(s.id AS LONG), " +
            "CAST(tas.id AS LONG), " +
            "s.name, " +
            "s.studentCode, " +
            "CAST(s.gender AS INTEGER), " +
            "CAST(CASE WHEN tas.id IS NULL THEN 0 ELSE 1 END AS INTEGER), " +
            "COALESCE(CAST(tas.totalScore AS FLOAT), 0), " +
            "CAST(COALESCE(tas.grade, 0) AS INTEGER), " +
            "CAST(CASE " +
            "    WHEN g.stage = 0 THEN g.grade " +
            "    WHEN g.stage = 1 THEN g.grade + 6 " +
            "    WHEN g.stage = 2 THEN g.grade + 9 " +
            "    WHEN g.stage = 3 THEN g.grade + 12 " +
            "    ELSE 0 " +
            "END AS INTEGER)) " +
            "FROM Student s " +
            "LEFT JOIN TestAssessmentSummary tas ON s.id = tas.studentId AND tas.testYear = :testYear " +
            "LEFT JOIN Clazz c ON s.classId = c.id " +
            "LEFT JOIN Grade g ON c.gradeId = g.id " +
            "WHERE s.classId = :classId")
    Page<StudentGeneralReviewVO> findStudentGeneralReviewsForTeacher(
            @Param("classId") Long classId,
            @Param("testYear") Integer testYear,
            Pageable pageable);



    /**
     * 根据学生id查询其所在年级id
     * @param studentId 学生id
     */
    @Query("SELECT c.gradeId FROM Student s JOIN Clazz c ON s.classId = c.id WHERE s.id = :studentId")
    Long findGradeIdByStudentId(@Param("studentId") Long studentId);



}